package com.neuedu;

import java.sql.*;

public class HiveToMySQLD {
    public static void main(String[] args) {
        // Hive 和 MySQL 的连接信息
        String hiveJdbcUrl = "jdbc:hive2://192.168.47.101:10000/vehicledb"; // Hive JDBC URL
        String mysqlJdbcUrl = "jdbc:mysql://192.168.47.101:3306/vehicledb"; // MySQL JDBC URL
        String mysqlUser = "root";
        String mysqlPassword = "123456";

        Connection hiveConn = null;
        Connection mysqlConn = null;
        Statement hiveStmt = null;
        ResultSet rs = null;
        PreparedStatement mysqlStmtPrepared = null;

        try {
            // 1. 连接到 Hive
            Class.forName("org.apache.hive.jdbc.HiveDriver");
            hiveConn = DriverManager.getConnection(hiveJdbcUrl, "", "");
            hiveStmt = hiveConn.createStatement();

            // 2. 从 Hive 查询数据
            String hiveQuery = "SELECT times, chongqing, beijing, guangdong, shanghai, zhejiang, anhui, tianjin, sichuan, shandong, jiangsu, dc_china, ac_china FROM d_china_charging_pile";
            rs = hiveStmt.executeQuery(hiveQuery);

            // 3. 连接到 MySQL
            Class.forName("com.mysql.cj.jdbc.Driver");
            mysqlConn = DriverManager.getConnection(mysqlJdbcUrl, mysqlUser, mysqlPassword);

            // 4. 创建 MySQL 插入语句
            String insertQuery = "INSERT INTO d_china_charging_pile (times, chongqing, beijing, guangdong, shanghai, zhejiang, anhui, tianjin, sichuan, shandong, jiangsu, dc_china, ac_china) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            mysqlStmtPrepared = mysqlConn.prepareStatement(insertQuery);

            // 5. 将数据从 Hive 导入 MySQL
            while (rs.next()) {
                // 提取 Hive 查询结果的数据
                Date times = rs.getDate("times");
                int chongqing = rs.getInt("chongqing");
                int beijing = rs.getInt("beijing");
                int guangdong = rs.getInt("guangdong");
                int shanghai = rs.getInt("shanghai");
                int zhejiang = rs.getInt("zhejiang");
                int anhui = rs.getInt("anhui");
                int tianjin = rs.getInt("tianjin");
                int sichuan = rs.getInt("sichuan");
                int shandong = rs.getInt("shandong");
                int jiangsu = rs.getInt("jiangsu");
                int dcChina = rs.getInt("dc_china");
                int acChina = rs.getInt("ac_china");

                // 设置 MySQL PreparedStatement 参数
                mysqlStmtPrepared.setDate(1, times);
                mysqlStmtPrepared.setInt(2, chongqing);
                mysqlStmtPrepared.setInt(3, beijing);
                mysqlStmtPrepared.setInt(4, guangdong);
                mysqlStmtPrepared.setInt(5, shanghai);
                mysqlStmtPrepared.setInt(6, zhejiang);
                mysqlStmtPrepared.setInt(7, anhui);
                mysqlStmtPrepared.setInt(8, tianjin);
                mysqlStmtPrepared.setInt(9, sichuan);
                mysqlStmtPrepared.setInt(10, shandong);
                mysqlStmtPrepared.setInt(11, jiangsu);
                mysqlStmtPrepared.setInt(12, dcChina);
                mysqlStmtPrepared.setInt(13, acChina);

                // 执行插入
                mysqlStmtPrepared.executeUpdate();
            }

            System.out.println("Data successfully transferred from Hive to MySQL");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 6. 关闭连接和资源
            try {
                if (rs != null) rs.close();
                if (hiveStmt != null) hiveStmt.close();
                if (hiveConn != null) hiveConn.close();
                if (mysqlStmtPrepared != null) mysqlStmtPrepared.close();
                if (mysqlConn != null) mysqlConn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

